Introduction

This month we present a series of assorted date and time routines that
illustrate ways to handle dates for business calendar needs. Some are new and
a number of others were published in previous issues of TechNotes.  We have
brought them together to give a more authoritative list of techniques and
programs.  We hope you find them useful.


Setting the System DATE and TIME

There is no command in dBASE III or dBASE III PLUS to change the system time
and date.  To change either or both of them, RUN the DOS programs DATE and
TIME.  The desired date and time can be placed in a memory variable and passed
to the operating system.  For example, 

   * ---Set DOS system date.
   today = "11-01-86"
   RUN DATE &today
   
   * ---Set DOS system time.
   now = "08:00"
   RUN TIME &now
   
Excerpted from the March, 1985 issue of TechNotes.


Date Formatting

To create a custom date display, you can convert a date variable to a
formatted string by using SUBSTR(), STR(), and date functions in combination. 
If, for example, you have a date in and you want the output to look like
"January 25, 1984," the expression that makes the conversion is,

   CMONTH(<date>) + " "  + STR(DAY(<date>),  2) +;
                    ", " + STR(YEAR(<date>), 4)

In this expression, the first portion of the expression,

   CMONTH(<date>)

returns the month name as a character string.  The second portion of the
expression,

   STR(DAY(<date>), 2)

returns the numeric day of the month as a character string.  The last portion
of the expression,

   STR(YEAR(<date>), 4)

returns the year as a four-character string.  The results of this last
expression are concatenated to the month and day.  This expression can be used
as the field contents in a REPORT FORM, a LABEL FORM, or other display command
that accepts a character expression such as ? or @...SAY.

General Date Formats

The following, Fdate.PRG, is a general purpose date formatting procedure.  It
takes two inputs, a date variable to format and a numeric code designating the
format desired, and produces one output, a character variable containing the
formatted date string.  Call Fdate.PRG with the following general syntax.

   * ---Receiving variable must exist prior to call.
   <return variable> = ""
   DO Fdate WITH <date to format>,;
                 <format code>,;
                 <return variable>
   
Fdate.PRG supports several date formats.  Pick from the following list using
the position in the list as the format code parameter when you DO Fdate.

   1. Mon DD, YYYY 
   2. Mon YYYY
   3. Mon DD
   4. Month DD, YYYY
   5. DD-Mon-YYYY
   
Note that the day format for formats 1, 3, and 4, do not include a leading
zero.  If the day of the month is less than ten, the day will be a single
digit number.  For format 5, the day format includes a leading zero for a day
of the month less than ten.

* Program..: Fdate.PRG
* Author...: Christopher White
* Date.....: November 1, 1986
*            published originally January 25, 1985
* Version..: dBASE III PLUS
* Note(s)..: This program takes a date variable passed to it
*            with the PARAMETERS phrase and returns the date
*            as a formatted character string.
*
PRIVATE ALL
PARAMETERS date, code, datestrg
DO CASE
CASE code = 1
     * ---"Nov 1, 1986"
     datestrg = SUBSTR(CMONTH(date), 1, 3) + " "  +;
                LTRIM(STR(DAY(date), 2))   + ", " +;
                STR(YEAR(date), 4)
CASE code = 2
     * ---"Nov 1986"
     datestrg = SUBSTR(CMONTH(date), 1, 3) + " " +;
                STR(YEAR(date), 4)
CASE code = 3
     * ---"Nov 1"
     datestrg = SUBSTR(CMONTH(date), 1, 3) + " " +;
                LTRIM(STR(DAY(date), 2))
CASE code = 4
     * ---"November 1, 1986"
     datestrg = CMONTH(date) + " " +;
                LTRIM(STR(DAY(date), 2)) + ", " + STR(YEAR(date), 4)
CASE code = 5
     * ---"01-Nov-1986"
     datestrg = LEFT(DTOC(date), 2) + "-" +;
                SUBSTR(CMONTH(date), 1, 3) + "-" +;
                STR(YEAR(date), 4)
OTHERWISE
     datestrg = "Error"
ENDCASE
RETURN
* EOP Fdate.PRG


Excerpted from the March, 1985 issue of TechNotes.


Working Days

Weekday1

The following two programs, Weekday1.PRG and Weekday2.PRG, show how to account
for weekend days when determining a number of working days.  Weekday1.PRG
allows you to select a start date and a number of days and will provide the
number of working days inclusive. 

To setup Weekday1.PRG or Weekday2.PRG, first CREATE Holiday.DBF with the
following structure,

   Structure for database: Holiday.DBF
   Field  Field Name  Type       Width    Dec
       1  Holiday     Date           8

Then, APPEND all the holidays that span the range of dates in which you will
be working.  This means that if you are working with a span of two years, you
must have APPENDed two records for each holiday, one for each specific date.  

To execute Weekday1.PRG, use the following general syntax after initializing a
return variable for the number of weekdays. 

   ret_val = ""
   DO Weekday1 WITH <start date>, <number of days>, ret_val
   
* Program ...: Weekday1.PRG
* Author ....: Kenneth N. Getz
* Date ......: November 1, 1986
* Version ...: dBASE III PLUS
* Note(s) ...: Program to find number of working days given
*              a starting date and the numbers of days hence.
*
PRIVATE ALL
PARAMETERS start, days, weekdays
* ---Get starting date to be a week day.
DO WHILE MOD(DOW(start) - 1, 6) = 0
   start = start + 1
   days  = days  - 1
ENDDO
* ---Subtract weekend days.
weekdays = days - INT(days / 7) * 2
* ---Find ending day. 
endday = DOW(start + days - 1)
* ---Subtract for each weekend day.
IF MOD(endday - 1, 6) = 0
   weekdays = IIF(endday = 1, weekdays - 2, weekdays - 1)
ENDIF
USE Holiday
* ---Data file storing holidays.
COUNT TO counter FOR  holiday >= start;
                .AND. holiday <= start + days;
                .AND. MOD(DOW(holiday) - 1, 6) <> 0
USE
* ---Subtract number of holidays.
weekdays = weekdays - counter
RETURN
* EOP Weekday1.PRG


Weekday2

Weekday2.PRG is a revision of Weekday1.PRG that allows you to specify a range
of dates and returns the number of work days between them.  To execute
Weekday2.PRG, use the following general syntax after initializing a return
variable for the number of work days returned from the routine.

   ret_val = ""
   DO Weekday2 WITH <start date>, <end date>, ret_val

* Program ...: Weekday2.PRG
* Author ....: Kenneth N. Getz
* Date ......: November 1, 1986 (Modified March 16, 1987)
* Version ...: dBASE III PLUS
* Note(s) ...: Program to find number of working days given
*              a starting and ending date. Includes both end 
*	 				dates.
*
PRIVATE ALL
PARAMETERS start, enddate, weekdays
days = enddate - start + 1
* ---Get starting date to be a week day.
DO WHILE MOD(DOW(start) - 1, 6) = 0
	start = start + 1
  	days  = days - 1
ENDDO
* ---Get ending date to be a week day.
DO WHILE MOD(DOW(enddate) - 1,6) = 0
	enddate = enddate - 1
	days    = days - 1
ENDDO	
* ---Subtract off weekend days.
weekdays = days - INT(days / 7) * 2
USE Holiday
COUNT TO counter FOR   holiday >= start;
                 .AND. holiday <= start + days;
                 .AND. MOD(DOW(holiday)-1,6) <> 0
USE
weekdays = weekdays - counter
RETURN
* EOP Weekday2.PRG



Twelve-Hour Time Format

If you find the standard twenty-four--hour clock confusing, you may want to
use the following procedure, Ampm.PRG, to display the time in the more
familiar twelve-hour format.

Ampm.PRG works by using the SUBSTR() function to separate the hours and
minutes from the time string you pass.  These elements are then converted to
the twelve-hour format.  To execute Ampm.PRG, use the following general syntax
after initializing a return variable,

   ret_val = ""
   DO Ampm WITH <time string>, ret_val
   
* Program ...: Ampm.PRG
* Author ....: Ray Cortenbach and Kenneth N. Getz
* Date ......: November 1, 1986
* Note(s)....: Returns time in 12-hour format (midnight is am, *             
noon is pm).
*
PRIVATE ALL
PARAMETERS m_time, hours
hours   = SUBSTR(m_time, 1, 2)
minutes = SUBSTR(m_time, 3, 3)
ampm    = IIF(hours >= "12"," pm"," am")
clock_hours = MOD(VAL(hours),12)
hours   = IIF(clock_hours = 0, "12", LTRIM(STR(clock_hours)))
hours = hours + minutes + ampm
RETURN
* EOP Ampm.PRG

If you need this time format for a REPORT or LABEL FORM, you can use the
following expression instead of Ampm.PRG.

   IIF(VAL(<time>) < 12, <time> + " am",;
      IIF(VAL(<time>) = 12, <time> + " pm",;
         STR(VAL(<time>) - 12 ,2) + SUBSTR(<time>, 3) + " pm"))


Displaying Days

Another date format you may need is the form "Friday November 1st, 1986."  The
following program converts a date to a character string and adds the
appropriate "th", "st", "nd", "rd" to the day.  To execute Day.PRG, use the
following general syntax after initializing a return variable.

   ret_val = ""
   DO Day WITH <date>, ret_val

* Program ...: Day.PRG
* Author ....: Ray Cortenbach and Kenneth N. Getz
* Date ......: November 1, 1986
* Note(s)....: Returns date in format "Friday November 1st, 1986"
*
PRIVATE ALL
PARAMETERS m_date, current
daynum = DAY(m_date)
m_day  = LTRIM(STR(daynum, 2))
DO CASE
CASE daynum = 2 .OR. daynum = 22
     m_day = m_day + "nd"
CASE daynum = 3 .OR. daynum = 23
     m_day = m_day + "rd"
CASE MOD(daynum, 10) = 1 .AND. daynum <> 11
     m_day = m_day + "st"
OTHERWISE 
     m_day = m_day + "th"
ENDCASE
current = CDOW(m_date) + " " + CMONTH(m_date) + " " +;
          m_day + ", " + STR(YEAR(m_date), 4)
RETURN
* EOP Day.PRG


Averaging Dates

Sometimes you may need to AVERAGE date fields in a database file.  For
example, you may want to know the average date of employment for all employees
or the average date of maturity for a group of bonds.

Since the date data type is handled differently in dBASE III PLUS than the
numeric data type, the AVERAGE command does not support averaging of dates but
does numerics.  To perform the AVERAGEing operation, you must convert the date
fields you want to AVERAGE to a numeric values.  To do this, subtract one of
the earliest dates in the dBASE III PLUS calendar, 01/01/0001 from your date
field and then the result can then be AVERAGEd.  After AVERAGEing of the
numeric values is complete, convert the result back to a date by adding it to
the original date.  The following sequence of commands demonstrates how to do
this:

   USE <database file>
   AVERAGE <your date field> - CTOD("01/01/0001") TO avg
   avgdate = CTOD("01/01/0001") + avg
   
You can also use this procedure that returns the average date from a date
field that you pass as a parameter in place of these commands.  To execute it,
use the following general syntax after initializing a return variable.

   ret_val = CTOD("01/01/01")
   DO Dateavg WITH "<date field to average>", ret_val

* Program ...: Dateavg.PRG
* Author ....: Brenda Johnson-Grau, based on a program
*              by Steve Silverwood
* Date ......: November 1, 1986
* Note(s) ...: Procedure to return average of date field in a
*              database file.
*
PRIVATE ALL
PARAMETERS date_fld, avgdate
AVERAGE &date_fld. - CTOD("01/01/0001") to avg
avgdate = CTOD("01/01/0001") + avg
RETURN
* EOP Dateavg.PRG


Calculating Ages

If you need to calculate and display a person's age according to the current
date (rather than estimate from the year alone), you need to test both current
month for the birth month and the current date for the birth date.  The
following procedure, Age.PRG, takes a date passed to it and displays the
person's age in the format: "x years old", where x is the calculated age.

* Program ...: Age.PRG
* Author ....: Beth Miranda
* Date ......: November 1, 1986
* Note(s) ...: Calculates a person's age from today's date.
* Version....: dBASE III
*
PARAMETERS birthdate
age = 0
currtmon = MONTH(DATE())
birthmon = MONTH(birthdate)
IF YEAR(DATE()) > YEAR(birthdate)
   age = YEAR(DATE()) - YEAR(birthdate)
   IF currtmon < birthmon .OR. ( currtmon = birthmon;
                .AND. DAY(DATE()) < DAY(birthdate))
      age = age - 1
   ENDIF
ENDIF
? STR(age,4) + " years old"
RETURN
* EOP Age.PRG

In dBASE III PLUS, you can replace the program above with the following
expression for use in REPORT and LABEL FORMs.

   ? IIF(YEAR(DATE())>YEAR(d2),;
       IIF(MONTH(DATE())<MONTH(d2).OR.(MONTH(DATE())=MONTH(d2);
           .AND.DAY(DATE())<DAY(d2)),;
            YEAR(DATE())-YEAR(d2)-1,;
            YEAR(DATE())-YEAR(d2);
        ),;
        0;
     )
   
Be aware that this expression is 217 bytes long.  12 bytes are allocated for
the "d2" variable name.  You can expand the variable name length to 10
characters but the expression will be 253 characters, precariously close to
the 254-character limit on the length of command lines.  If you plan to use
this expression keep your date variable lengths short.


Indexing Dates

INDEXing ON a date is quite easy.  For example, the command:

     INDEX ON <date field> TO <date index>

will produce an index in chronological order by year, month, and day.  

However, a problem arises when INDEXing ON a compound key that includes a date
field which is common in the majority of applications.  A date is rarely the
unique identifier or the sole determinant of order.  Suppose that you have a
transaction-oriented application.  Typically, the transactions database file
will be INDEXed ON the date of transaction and some form of customer
identification.  The logical order is time-oriented, but the customer order
must be maintained.  Or, the customer identification is the primary key and
the date of transaction the secondary key--a type of ordering useful for
invoicing, account history reporting, and aging analysis.  

Multiple Key Ascending Order

Defining an INDEX expression as a compound key requires that the key
expression evaluate to a single data type.  In most instances, this data type
will be character.  The character data type offers concatenation, a powerful
tool to combine elements of an expression, which preserves a left-to-right
precedence of order.

When defining a character expression that includes a date variable, your
natural inclination might be to use DTOC() to convert the date field to a
character string, and in most cases it would be a valid assumption.  INDEXing
with the DTOC() function, however, does not yield a valid date order. 
Instead, DTOC() returns as a character value, the date in the format specified
by the SET DATE command.  The default is MM/DD/YY, the American format.  You
could SET DATE ANSI and then INDEX ON the DTOC() of the date field, but this
approach has its hazards.  In order to maintain the integrity of the index
file, DATE must be SET ANSI whenever the index is updated.  However, the
display format preferred in the United States is not ANSI.  A considerable
amount of work would be required to make sure that every change to the
database file is prefaced by a SET DATE ANSI and followed by a SET DATE
AMERICAN for display operations.

The preferred method of INDEXing ON the date field with a compound key
expression is to use the YEAR(), MONTH(), DAY(), and the STR() functions. 
This will create the correct hierarchy of order, converting the date to a
character string.  A example might look like,

   * ---Create index file.
   INDEX ON STR(YEAR(Date),4) + STR(MONTH(Date),2);
          + STR(DAY(Date),2) + Custnum TO DateNdx
   
   * ---Look up entry in the index file.
   transdate = CTOD("  /  /  ")
   customer  = SPACE(5)
   @ 10,10 SAY "Enter transactions date " GET transdate
   @ 11,10 SAY "  Enter customer number " GET customer
   READ
   SEEK STR(YEAR(transdate),4) + STR(MONTH(transdate),2);
       + STR(DAY(transdate),2) + customer

Be sure that whenever you use STR() in an index expression, to specify a
length parameter.  Failure to do so results in the error message "Record not
in index" whenever the index file is used. 

Descending Order

INDEXing dates in descending order provides a new twist.  dBASE III and dBASE
III PLUS does not directly support a descending order index file as dBASE II
does.  Therefore, an algorithm must be devised to create a descending order
and integrated with the date so that the hierarchy of order -- year, month,
and day -- is preserved. 

In creating a descending order index file, the general idea is to subtract the
key value from a number larger than the largest possible key value and pass
that to the index mechanism as the new key value.  This reverses the order of
keys, creating a descending order.  To use this algorithm, a date must be
converted into a number that is in the form YYYYMMDD.  This can be done as
follows:

   YEAR(<date>) * 10000 + MONTH(<date>) * 100 + DAY(<date>)

If today's date is 11/01/86, the result of the expression will be 19850801. 
Notice that the integrity of the date is preserved and the result is in a form
that can be subtracted from a very large number.

To INDEX a date in descending order, assemble the final algorithm and execute
it in an INDEX command.  For example:

     INDEX ON STR(9999999 - (YEAR(<date>)*10000;
           + MONTH(<date>)*100 + DAY(<date>)),8) TO <your index>

Essentially, INDEXing ON dates successfully requires some simple conversions
and calculations. 

Excerpted from the August, 1985 issue of TechNotes.


Scrolling Date Selectors

Introduction

Perhaps you would like to set up an application where you can easily select
specific dates or periods.  The following programs, Scroll1.PRG and
Scroll2.PRG, allow you to do this.  Each displays the date or date range in a
reverse video window allowing you scroll forward or back in time with the
Uparrow and Dnarrow keys.  Pressing Return selects the date or date period
displayed.  Pressing Esc returns a blank or the seed date passed.  

For both programs, you pass the row and column coordinates of the scroll
window and a return variable as parameters.


Scroll Time Periods

Scroll1.PRG allows you to scroll through time periods beginning with the
closest period to the system date.  The period begins on the closest Monday to
the current date and ends on the Friday of the next week.  To change the
length and the last day of the period, change the initial values of the memory
variables "period" and "per_end". 

Pressing Esc returns a blank date.  Pressing Return, returns the beginning
date of the time period your have selected.  

To execute Scroll1.PRG, use following commands,

   ret_val = CTOD("  /  /  ")
   DO Scroll1 WITH <row>, <col>, ret_val

* Program ...: Scroll1.PRG
* Author ....: Christopher White
* Date ......: November 1, 1986
* Note(s) ...: Selects a time period from a scrolling menu.
*
PRIVATE ALL
PARAMETERS row, col, seed
* ---Key definitions.
esc      = CHR(27)
uparrow  = CHR(5)
dnarrow  = CHR(24)
enter    = CHR(13)
* ---Initialize dates.
period  = 14
per_end = period - 3
start   = CTOD("01/08/1900")
seed    = DATE() - (MOD(DATE() - start, period) + period)
DO WHILE .T.
   SET COLOR TO N/W
   @ row, col SAY LEFT(CDOW(seed),3) + " "  + DTOC(seed)   +;
                  " thru " + LEFT(CDOW(seed + per_end), 3) +;
                  " " + DTOC(seed + per_end)
   SET COLOR TO W/N
   key = CHR(0)
   * ---Get key.
   DO WHILE .NOT. key $ esc + enter + dnarrow + uparrow
      key = UPPER(CHR(INKEY()))
   ENDDO
   DO CASE
   CASE key = dnarrow
        seed = seed + period
   CASE key = uparrow
        seed = seed - period
   CASE key = enter 
        EXIT
   CASE key = esc
        seed = CTOD("  /  /  ")
        EXIT
   ENDCASE
ENDDO
RETURN
* EOP Scroll1.PRG


Scroll Days

Scroll2.PRG allows you to scroll through consecutive days beginning with the
date you pass in the memory variable, "ret_val."  Pressing Return returns the
date you selected in the variable "ret_val."  Pressing Esc, returns the seed
date.  Subsequent runs of the program uses the last value stored in "ret_val"
for the seed date.  This is useful so that you can carry forward selected
dates.

To execute Scroll2.PRG, use the following commands.

   ret_val = DATE()
   DO Scroll2 WITH <row>, <col>, ret_val

* Program ...: Scroll2.PRG
* Author ....: Christopher White
* Date ......: November 1, 1986
* Note(s) ...: Selects a day from a scrolling menu.
*
PRIVATE ALL
PARAMETERS row, col, seed
* ---Key definitions.
esc      = CHR(27)
uparrow  = CHR(5)
dnarrow  = CHR(24)
enter    = CHR(13)
* ---Initialize dates.
start   = CTOD("01/08/1900")
b_date  = seed
DO WHILE .T.
   SET COLOR TO N/W
   @ row, col SAY LEFT(CDOW(seed),3)+" "+DTOC(seed)
   SET COLOR TO W/N
   key = CHR(0)
   * ---Get key.
   DO WHILE .NOT. key $ esc + enter + dnarrow + uparrow
      key = UPPER(CHR(INKEY()))
   ENDDO
   DO CASE
   CASE key = dnarrow
        seed = seed + 1
   CASE key = uparrow
        seed = seed - 1
   CASE key = enter
        EXIT
   CASE key = esc
        seed = b_date
        EXIT
   ENDCASE
ENDDO
RETURN
* EOP Scroll2.PRG


Changing a Character Field to a Date Field

You can convert character fields that contain dates in the form "mm/dd/yy" or
any of the formats specified by the SET DATE command to dBASE III PLUS date
fields without losing the date value.  You do this by MODIFYing the STRUCTURE
of the database file and changing the field that contians the date information
from character to date type.  Saving this change converts all dates stored in
the character field as "mm/dd/yy" directly to dBASE III PLUS date values.

This is useful when converting dBASE II database files to dBASE III PLUS.  

Excerpted from the February, 1985, issue of TechNotes.